//
//  YJStatusDAL.swift
//  YJWeiBo
//
//  Created by Jim1024 on 2017/1/24.
//  Copyright © 2017年 Jim1024. All rights reserved.
//

import UIKit

// CREATE TABLE IF NOT EXISTS T_Status (statusId INTEGER NOT NULL PRIMARY KEY, status TEXT, userId INTEGER, createTime TEXT DEFAULT (datetime('now', 'localtime')));

/// 加载数据工具
class YJStatusDAL: NSObject {
    static let shared  = YJStatusDAL()
    
    fileprivate override init() {
        super.init()
    }
    
    /// 默认数据过期时间为一个星期
    let cacheDateTime: TimeInterval = 7 * 24 * 60 * 60
    
    /// 清除本地缓存
    func clearCacheData() {
        // 计算过期时间
        let overDate = Date(timeIntervalSinceNow: -cacheDateTime)
        
        let df = DateFormatter()
        df.locale = Locale(identifier: "EN")
        df.dateFormat = "yyyy-MM-dd HH:mm:ss"
        let overTime = df.string(from: overDate)
        JPrint("overTime: \(overTime)")
        
        let sql = "DELETE FROM T_Status WHERE createTime < '\(overTime)'"
        JPrint("清除sql: \(sql)")
        
        YJSQLiteManager.shared.dbQueue.inDatabase { (db) in
            do {
                try db?.executeUpdate(sql, values: [])
                print("清除缓存成功了")
            } catch let error as NSError {
                print("清除缓存失败了: \(error)")
            }
        }
    }
    
    /// 数据访问层加载微博数据
    func dalLoadStatus(_ since_id: Int64, max_id: Int64, dalLoadStatusCallback: @escaping (_ status: [[String: Any]]?, _ error: NSError?) -> ()) {
        // 1.先去本地数据加载
        loadCacheStatus(since_id, max_id: max_id) { (statuses, error) in
            if statuses != nil && (statuses?.count)! > 0 {
                // 2.如果本地加载到数据,直接返回数据
                JPrint("加载本地微博数据")
                dalLoadStatusCallback(statuses, nil)
                return
            }
            
            // 3.如果本地没有数据,使用网络工具类加载网络数据
            let urlString = "https://api.weibo.com/2/statuses/friends_timeline.json"
            
            var params = [String : Any]()
            
            // 表示传递了since_id需要下拉刷新,返回比since_id大的微博
            if since_id > 0 {
                params["since_id"] = NSNumber(value: since_id)
            } else if max_id > 0 {
                params["max_id"] = NSNumber(value: max_id - 1)
            }
            
            // 每次请求两条微博
            //        params["count"] = 2
            
            YJNetworkManager.request(urlStrig: urlString, parameters: params) { (result, error) in
                
                if error != nil {
                    JPrint("加载微博网络请求失败: \(error)")
                    dalLoadStatusCallback(nil, error)
                    return
                }
                
                // 没有出错,解析返回的数据中的 "statuses", statuses里面对应的是所有的微博数据
                // [[String: AnyObject]]: 数组,数组里面存的是字典类型的元素
                let statusArray = result?["statuses"] as? [[String : Any]]
                
                if statusArray != nil {
                    // 4.将加载的网络数据保存到本地 *
                    self.saveStatus(statusArray!)
                    
                    // 5.将加载的网络数据返回
                    dalLoadStatusCallback(statusArray, nil)
                } else {
                    dalLoadStatusCallback(nil, nil)
                }
            }
        }
    }
    
    /// 从本地数据加载微博数据
    fileprivate func loadCacheStatus(_ since_id: Int64, max_id: Int64, loadCachedFinished: @escaping (_ statuses: [[String: Any]]?, _ error: NSError?) -> ()) {
        let userId = YJUserAccountViewModel.sharedInstance.userAccount?.uid
        assert(userId != nil, "没有UID")
        
        var sql = "SELECT status FROM T_Status \n"
        
        if since_id > 0 {
            sql += "WHERE statusId > \(since_id) AND userId = \(Int(userId!)!) \n"
        } else if max_id > 0 {
            sql += "WHERE statusId < \(max_id) AND userId = \(Int(userId!)!) \n"
        } else {
            sql += "WHERE userId = \(Int(userId!)!) \n"
        }
        
        sql += "ORDER BY statusId DESC LIMIT 20"
        
        YJSQLiteManager.shared.dbQueue.inDatabase { (db) in
            do {
                let result = try db?.executeQuery(sql, values: [])
                var statuses = [[String : AnyObject]]()
                
                while (result?.next())! {
                    // 获取微博数据,字符串
                    let string = result?.string(forColumn: "status")
                    
                    // 将String -> NSData -> 字典
                    let data = string?.data(using: String.Encoding.utf8)!
                    let dict = try! JSONSerialization.jsonObject(with: data!, options: JSONSerialization.ReadingOptions(rawValue: 0)) as! [String : AnyObject]
                    statuses.append(dict)
                }
                // 告诉调用的人本地加载到数据了
                loadCachedFinished(statuses, nil)
            } catch let error as NSError {
                JPrint("加载缓存数据出错了:\(error)")
                loadCachedFinished(nil, error)
            }
        }
    }
        
    /**
     保存微博数据
     
     - parameter statuses: 微博字典数据,最终将每个微博字典保存到数据库
     */
    fileprivate func saveStatus(_ statuses: [[String: Any]]) {
        let userId = YJUserAccountViewModel.sharedInstance.userAccount?.uid
        assert(userId != nil, "没有UID")
        
        let sql = "INSERT INTO T_Status (statusId, status, userId) VALUES (?, ?, ?)"
        
        YJSQLiteManager.shared.dbQueue.inTransaction { (db, rollback) in
            do {
                // 遍历获取每一条微博字典
                for dict in statuses {
                    // 微博id
                    let statusId = dict["id"] as! Int
                    
                    // 数据库中不能直接存储字典
                    // 字典转成 -> NSData -> String
                    let data = try! JSONSerialization.data(withJSONObject: dict, options: JSONSerialization.WritingOptions(rawValue: 0))
                    let status = String(data: data, encoding: String.Encoding.utf8)!
                    
                    try db?.executeUpdate(sql, values: [statusId, status, Int(userId!)!])
                }
                JPrint("保存了:\(statuses.count)条数据到数据库")
            } catch let error as NSError {
                JPrint("保存微博数据失败:\(error)")
            }
        }
    }
}
